import os
import mysql.connector
import pymysql
from pathlib import Path

try:
    # 连接数据库
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='root',
        database='ry-vue'
    )
    cursor = conn.cursor()

    # 遍历word-audio目录下的音频文件
    audio_dir = Path('C:/ruoyi/word-audio')
    for audio_file in audio_dir.glob('*.mp3'):
        if audio_file.stem in ['bad', 'dad', 'can']:
            # 读取音频文件
            with open(audio_file, 'rb') as f:
                audio_data = f.read()
            
            # 获取文件信息
            word = audio_file.stem
            audio_type = audio_file.suffix[1:]
            audio_size = os.path.getsize(audio_file)
            
            # 查询单词ID
            word_id_sql = 'SELECT word_id FROM sys_word_book WHERE word = %s'
            cursor.execute(word_id_sql, (word,))
            result = cursor.fetchone()
            word_id = result[0] if result else 0
            
            # 插入数据库
            sql = '''INSERT INTO sys_word_audio 
                    (word_id, word, audio_data, audio_type, audio_size, status, create_by, create_time)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, NOW())'''
            cursor.execute(sql, (word_id, word, audio_data, audio_type, audio_size, '0', 'admin'))
            print(f'导入音频文件：{word}.{audio_type}')

    # 提交事务并关闭连接
    conn.commit()
    cursor.close()
    conn.close()
    print('音频文件导入完成')
except Exception as e:
    print(f'导入失败：{str(e)}')